package io.github.jsp.tranvolume.mapper;

import io.github.jsp.tranvolume.entity.TranVolumeProvince;
import org.apache.ibatis.annotations.*;

import java.time.LocalDateTime;
import java.util.List;
import java.util.Map;

/**
 * 省份交易量Mapper
 */
@Mapper
public interface TranVolumeProvinceMapper {

    @Select("SELECT * FROM tran_volume_province WHERE minute_time BETWEEN #{startTime} AND #{endTime} ORDER BY minute_time DESC")
    List<TranVolumeProvince> findByTimeRange(@Param("startTime") LocalDateTime startTime, 
                                            @Param("endTime") LocalDateTime endTime);

    @Select("SELECT * FROM tran_volume_province WHERE minute_time BETWEEN #{startTime} AND #{endTime} " +
            "AND prov_code = #{provCode} ORDER BY minute_time DESC")
    List<TranVolumeProvince> findByTimeRangeAndProvCode(@Param("startTime") LocalDateTime startTime, 
                                                       @Param("endTime") LocalDateTime endTime,
                                                       @Param("provCode") String provCode);

    @Select("SELECT prov_code, SUM(total_count) as total_count, SUM(success_count) as success_count, SUM(fail_count) as fail_count " +
            "FROM tran_volume_province WHERE minute_time BETWEEN #{startTime} AND #{endTime} " +
            "GROUP BY prov_code ORDER BY total_count DESC")
    List<Map<String, Object>> getProvinceVolumeStats(@Param("startTime") LocalDateTime startTime, 
                                                     @Param("endTime") LocalDateTime endTime);

    @Insert("INSERT INTO tran_volume_province (minute_time, prov_code, center, service_plat, total_count, success_count, fail_count) " +
            "VALUES (#{minuteTime}, #{provCode}, #{center}, #{servicePlat}, #{totalCount}, #{successCount}, #{failCount}) " +
            "ON CONFLICT (minute_time, prov_code, center, service_plat) DO UPDATE SET " +
            "total_count = total_count + #{totalCount}, " +
            "success_count = success_count + #{successCount}, " +
            "fail_count = fail_count + #{failCount}")
    int insertOrUpdate(TranVolumeProvince record);

    @Select("SELECT DATE_TRUNC('minute', minute_time) as minute_time, " +
            "prov_code, SUM(total_count) as total_count, SUM(success_count) as success_count " +
            "FROM tran_volume_province WHERE minute_time >= NOW() - INTERVAL '30 minutes' " +
            "GROUP BY DATE_TRUNC('minute', minute_time), prov_code " +
            "ORDER BY minute_time DESC, total_count DESC")
    List<Map<String, Object>> getRecentProvinceData();
}